﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Nova_DAL
{
    public class Group
    {
        int i = 0;
        string strSQL = "";
        DataSet ds = null;
        DbHelper dbHelper = new DbHelper();


        /// <summary>
        /// 检查工作组编号是否已经存在
        /// </summary>
        /// <param name="strGroupNo"></param>
        /// <returns>返回int,0不存在;1存在</returns>
        /// writer:[Kitty]
        /// Create Date:[2010-5-27]
        public int CheckGroupNoExists(string strGroupNo)
        {
            strSQL = "select isnull(count(1),0) from tbGroup where vGroupNo = @vGroupNo";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
            dbHelper.AddInParameter(cmd, "@vGroupNo", DbType.String, strGroupNo);

            return Convert.ToInt32(dbHelper.ExecuteScalar(cmd));
        }

        /// <summary>
        /// 检查工作组编号是否已经存在,用于修改资料时判断
        /// </summary>
        /// <param name="strGroupNo"></param>
        /// <param name="oldGroupNo"></param>
        /// <returns>返回int,0不存在;1存在</returns>
        /// writer:[Kitty]
        /// Create Date:[2010-5-27]
        public int CheckGroupNoExists(string strGroupNo, string oldGroupNo)
        {
            strSQL = "select isnull(count(1),0) from tbGroup where vGroupNo = @vGroupNo and vGroupNo <> @oldGroupNo";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
            dbHelper.AddInParameter(cmd, "@vGroupNo", DbType.String, strGroupNo);
            dbHelper.AddInParameter(cmd, "@oldGroupNo", DbType.String, oldGroupNo);

            return Convert.ToInt32(dbHelper.ExecuteScalar(cmd));
        }

        ///<summary>
        ///获取工作组列表
        ///</summary>
        ///<param name="sQL">SQL</param>
        ///<returns>返回DataSet</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-20]
        ///</summary>
        public DataSet DataListGroup()
        {
            try
            {
                strSQL = "select PID,vGroupNo,vGroupName from tbGroup";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }

            return ds;
        }

        ///<summary>
        ///获取工作组列表
        ///</summary>
        ///<param name="vGroupNo">vGroupNo</param>
        ///<returns>返回DataSet</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-26]
        ///</summary>
        public DataSet DataListGroup(string vGroupNo)
        {
            try
            {
                strSQL = "select PID,vGroupNo,vGroupName from tbGroup where vGroupNo = '" + vGroupNo + "'";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }

            return ds;
        }

        ///<summary>
        ///获取用户工作组列表
        ///</summary>
        ///<param name="vUserNo">用户编号</param>
        ///<returns>返回DataSet</returnss>
        ///Writer：[夏鑫]
        ///Create Date： [2010-6-4]
        ///</summary>
        public DataSet DataListUserGroup(string vUserNo)
        {
            try
            {
                strSQL = "select u.PID as id, u.vUserNo as vUserNo, g.vPermit as vPermit from tbUser as u,tbGroupPermit as g where u.vGroupNo=g.vGroupNo and u.vUserNo='" + vUserNo + "'";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }

            return ds;
        }


        ///<summary>
        ///增加工作组
        ///</summary>
        ///<param name="sQL">vGroupNo,vGroupName</param>
        ///<returns>返回int,0失败,1成功</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-20]
        ///</summary>
        public int AddGroup(Nova_MOD.Group Group)
        {
            try
            {
                strSQL = "insert into tbGroup(vGroupNo,vGroupName) values(@vGroupNo,@vGroupName)";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vGroupNo", DbType.String, Group.vGroupNo);
                dbHelper.AddInParameter(cmd, "@vGroupName", DbType.String, Group.vGroupName);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }

        ///<summary>
        ///修改工作组
        ///</summary>
        ///<param name="sQL">vGroupNo,vGroupName,id</param>
        ///<returns>返回int,0失败,1成功</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-20]
        ///</summary>
        public int UpdateGroup(Nova_MOD.Group Group)
        {
            try
            {
                strSQL = "update tbGroup set vGroupNo =@vGroupNo,vGroupName=@vGroupName where PID =@PID";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@vGroupNo", DbType.String, Group.vGroupNo);
                dbHelper.AddInParameter(cmd, "@vGroupName", DbType.String, Group.vGroupName);
                dbHelper.AddInParameter(cmd, "@PID", DbType.String, Group.PID);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }

        ///<summary>
        ///删除工作组
        ///</summary>
        ///<param name="sQL">id</param>
        ///<returns>返回int,0失败,1成功</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-5-20]
        ///</summary>
        public int DeleteGroup(string PID)
        {
            try
            {
                strSQL = "delete from tbGroup where PID =@PID";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@PID", DbType.String, PID);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }


        ///<summary>
        ///删除多个工作组
        ///</summary>
        ///<param name="listGroupNo">id</param>
        ///<returns>返回int,0失败,大于等于1成功</returnss>
        ///Writer：[kitty]
        ///Create Date： [2010-5-27]
        ///</summary>
        public int DeleteAllGroup(string listGroupNo)
        {
            try
            {
                strSQL = "delete from tbGroup where vGroupNo in  (" + listGroupNo + ") ";
                DbCommand cmd = dbHelper.GetSqlStringCommond(strSQL);
                i = dbHelper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return i;
        }
    }
}
